package com.css.fxfzypg.modules.maps.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzypg.constants.FxfzConstants;
import com.css.fxfzypg.modules.maps.service.KzsfResultsMapService;
import com.css.fxfzypg.modules.saresults.entity.DzqhQpBasicMapEntity;
import com.css.fxfzypg.modules.saresults.entity.DzqhApCruxLayerPfEntity;
import com.css.fxfzypg.modules.saresults.vo.SaResultsHlVo;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysAreaService;
import com.css.fxfzypg.zcpt.sys.service.SysRoleService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.*;

/**
 * @Author zyt
 * @Date: 2022/12/27
 */
@Service
@Slf4j
public class KzsfResultsMapServiceImpl implements KzsfResultsMapService {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Autowired
    SysRoleService sysRoleService;

    @Autowired
    SUserService sUserService;

    @Autowired
    SysAreaService sysAreaService;

    //抗震设防成果目录项目信息地图圈选查询
    @Override
    public HashMap<String, Object> queryKzsfResultsProjectByGeomText(DzqhQpBasicMapEntity mapEntity, String geomText) {
        String userId = PlatformSessionContext.getUserID();
        String role = sysRoleService.getRoleByUserId(userId);
        String user = sUserService.getSUser(PlatformSessionContext.getUserID());
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        //单位id
        String orgId = sUser.getOrgId();
        //省级id
        String provinceId = sUser.getProvinceId();
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        ArrayList<String> roleList = new ArrayList<>();
        for (Map roleMap : roleMaps) {
            String roleCode = (String) roleMap.get("roleCode");
            roleList.add(roleCode);
        }
        if (roleList.contains(FxfzConstants.DZQH_GL_ROLE)) {
            //系统管理员
            mapEntity.setRole("0");
        } else if (roleList.contains(FxfzConstants.DZQH_PRI_GL_ROLE)) {
            //省级管理员
            mapEntity.setRole("1");
        } else if (roleList.contains(FxfzConstants.DZQH_GAME_GL_ROLE)) {
            //局级管理员
            mapEntity.setRole("2");
        } else {
            //普通用户
            mapEntity.setRole("3");
        }
        //抗震设防成果项目信息查询
        StringBuilder sql = new StringBuilder("select DISTINCT(project_id),* from (select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,safety_rating as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,sub_status,update_status,create_user,model,geom_text from dzqh_ap_basic_map" +
                " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,sub_status,update_status,create_user,model,geom_text from dzqh_qp_basic_map" +
                " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,sub_status,update_status,create_user,model,geom_text from dzqh_xqh_basic_map) as dzqh" +
                " where sub_status='1' and update_status='0'");
        if (mapEntity.getRole().equals("3")){
            sql.append(" and is_delete='0' and create_user='"+userId+"'");
        }
        if (mapEntity.getRole().equals("1")){
            sql.append(" and province_id='"+provinceId+"'");
        }
        if (mapEntity.getRole().equals("2")){
            sql.append(" and (province_id='"+provinceId+"' or org_id='"+orgId+"')");
        }
        //地图圈选范围查询
        if (!PlatformObjectUtils.isEmpty(geomText)) {
            //项目位置上传文件空间位置相交
            sql.append(" and geom_text is not null and geom_text!='' and ST_Intersects(st_geometryfromtext(geom_text,4490),st_geometryfromtext('"+geomText+"',4490))");
            //项目位置经纬度相交查询
            //sql.append(" and ST_Intersects(st_geometryfromtext('POINT(' || longitude || ' ' || latitude || ')',4490),st_geometryfromtext('"+geomText+"',4490))");
        }
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhQpBasicMapEntity.class)).size();
        sql.append(orderSql);
        List<DzqhQpBasicMapEntity> apAsicMapList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhQpBasicMapEntity.class));
        for (DzqhQpBasicMapEntity asicMap : apAsicMapList) {
            String region = asicMap.getAdministrativeRegion();
            asicMap.setAdministrativeRegion(sysAreaService.getAreaNameByAreaId(region));
        }
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", apAsicMapList);
        return resultMap;
    }

    //抗震设防成果目录孔位信息地图圈选查询
    @Override
    public HashMap<String, Object> queryKzsfResultsHlByGeomText(SaResultsHlVo saResultsHlVo, String geomText) {
        String userId = PlatformSessionContext.getUserID();
        String role = sysRoleService.getRoleByUserId(userId);
        String user = sUserService.getSUser(PlatformSessionContext.getUserID());
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        //单位id
        String orgId = sUser.getOrgId();
        //省级id
        String provinceId = sUser.getProvinceId();
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            String roleCode = (String) roleMap.get("roleCode");
            if (roleCode.equals(FxfzConstants.DZQH_GL_ROLE)) {
                //系统管理员
                saResultsHlVo.setRole("0");
            } else if(roleCode.equals(FxfzConstants.DZQH_PRI_GL_ROLE)){
                //省级管理员
                saResultsHlVo.setRole("1");
            }else if (roleCode.equals(FxfzConstants.DZQH_GAME_GL_ROLE)){
                //局级管理员
                saResultsHlVo.setRole("2");
            }else {
                //普通用户
                saResultsHlVo.setRole("3");
            }
        }
        //抗震设防成果孔位信息查询
        StringBuilder sql = new StringBuilder("select daclp.* from (select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,safety_rating as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,update_status,sub_status,create_user,model,geom_text from dzqh_ap_basic_map" +
                " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,update_status,sub_status,create_user,model,geom_text from dzqh_qp_basic_map" +
                " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,update_status,sub_status,create_user,model,geom_text from dzqh_xqh_basic_map) as dzqh" +
                " left join dzqh_ap_crux_layer_pf daclp on daclp.project_id = dzqh.project_id" +
                //" left join dzqh_ap_crux_sd dacsd on dacsd.project_id=dzqh.project_id" +
                //" left join dzqh_ap_crux_sc dacsc on dacsc.project_id=dzqh.project_id" +
                //" left join dzqh_ap_crux_sd dacs on dacs.project_id= dzqh.project_id" +
                " where dzqh.sub_status='1' and dzqh.update_status='0'");
        if (saResultsHlVo.getRole().equals("3")){
            sql.append(" and dzqh.is_delete='0' and dzqh.create_user='"+userId+"'");
        }
        if (saResultsHlVo.getRole().equals("1")){
            sql.append(" and dzqh.province_id='"+provinceId+"'");
        }
        if (saResultsHlVo.getRole().equals("2")){
            sql.append(" and (dzqh.province_id='"+provinceId+"' or dzqh.org_id='"+orgId+"')");
        }
        //有孔位信息
        sql.append(" and daclp.project_id is not null and daclp.hl_longitude is not null and daclp.hl_latitude is not null");
        //地图圈选范围查询
        if (!PlatformObjectUtils.isEmpty(geomText)) {
            //项目信息相交
            //sql.append(" and dzqh.geom_text is not null and dzqh.geom_text!='' and ST_Intersects(st_geometryfromtext(dzqh.geom_text,4490),st_geometryfromtext('"+geomText+"',4490))");
            //孔位信息相交
            sql.append(" and ST_Intersects(st_geometryfromtext('POINT(' || daclp.hl_longitude || ' ' || daclp.hl_latitude || ')',4490),st_geometryfromtext('"+geomText+"',4490))");
        }
        //安评孔位信息
        StringBuilder orderSql = new StringBuilder(" order by daclp.project_id,daclp.create_time desc");
        sql.append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhApCruxLayerPfEntity.class)).size();
        List<DzqhApCruxLayerPfEntity> cruxLayerPfList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhApCruxLayerPfEntity.class));
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", cruxLayerPfList);
        return resultMap;
    }

    //局级成果项目信息地图圈选查询
    @Override
    public HashMap<String, Object> queryBureauLevelResultsProjectByGeomText(DzqhQpBasicMapEntity mapEntity, String geomText) {
        String userId = PlatformSessionContext.getUserID();
        String role = sysRoleService.getRoleByUserId(userId);
        String user = sUserService.getSUser(PlatformSessionContext.getUserID());
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        //单位id
        String orgId = sUser.getOrgId();
        //省级id
        String provinceId = sUser.getProvinceId();
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            String roleCode = (String) roleMap.get("roleCode");
            if (roleCode.equals(FxfzConstants.DZQH_GAME_GL_ROLE)){
                //局级管理员
                mapEntity.setRole("2");
                break;
            }
        }
        if(!PlatformObjectUtils.isEmpty(mapEntity.getRole()) && mapEntity.getRole().equals("2")){//局级管理员
            //局级成果项目信息查询
            StringBuilder sql = new StringBuilder("select DISTINCT(project_id),* from (select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,safety_rating as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,sub_status,update_status,create_user,model,geom_text from dzqh_ap_basic_map" +
                    " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,sub_status,update_status,create_user,model,geom_text from dzqh_qp_basic_map" +
                    " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,sub_status,update_status,create_user,model,geom_text from dzqh_xqh_basic_map) as dzqh" +
                    " where sub_status='1' and update_status='0'");
            //局级成果
            sql.append(" and (province_id='"+provinceId+"' or org_id='"+orgId+"')");

            if (!PlatformObjectUtils.isEmpty(mapEntity.getProjectName())) {
                sql.append(" and project_name like '%" + mapEntity.getProjectName() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getLongitude())) {
                sql.append(" and longitude >= '"+mapEntity.getLongitude()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(mapEntity.getEndLongitude())){
                sql.append(" and longitude <= '"+mapEntity.getEndLongitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getLatitude())) {
                sql.append(" and latitude >= '"+mapEntity.getLatitude()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(mapEntity.getEndLatitude())){
                sql.append(" and latitude <= '"+mapEntity.getEndLatitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getApprovalStatus())) {
                sql.append(" and approval_status ='" + mapEntity.getApprovalStatus() + "'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getReviewUnit())) {
                sql.append(" and review_unit like '%" + mapEntity.getReviewUnit() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getStatus())) {
                sql.append(" and status = '" + mapEntity.getStatus() + "'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getAdministrativeRegion())) {
                sql.append(" and administrative_region= '" + mapEntity.getAdministrativeRegion() + "'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getUndertaker())) {
                sql.append(" and undertaker like '%" + mapEntity.getUndertaker() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getRegistrant())) {
                sql.append(" and registrant like '%" + mapEntity.getRegistrant() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getRegistrationUnit())) {
                sql.append(" and registration_unit like '%" + mapEntity.getRegistrationUnit() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getSr())) {
                sql.append(" and sr ='" + mapEntity.getSr() + "'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getCreateStartTime())) {
                sql.append(" and create_time >= '" + mapEntity.getCreateStartTime()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(mapEntity.getCreateEndTime())){
                sql.append(" and create_time <= '" + mapEntity.getCreateEndTime()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getSubStartTime()) && !PlatformObjectUtils.isEmpty(mapEntity.getSubEndTime())) {
                sql.append(" and sub_date >= '" + mapEntity.getSubStartTime() + "' and sub_date <='" + mapEntity.getSubEndTime() + "'");
            }

            //地图圈选范围查询
            if (!PlatformObjectUtils.isEmpty(geomText)) {
                //项目位置上传文件空间位置相交
                sql.append(" and geom_text is not null and geom_text!='' and ST_Intersects(st_geometryfromtext(geom_text,4490),st_geometryfromtext('"+geomText+"',4490))");
                //项目位置经纬度相交查询
                //sql.append(" and ST_Intersects(st_geometryfromtext('POINT(' || longitude || ' ' || latitude || ')',4490),st_geometryfromtext('"+geomText+"',4490))");
            }
            StringBuilder orderSql = new StringBuilder(" order by create_time desc");
            Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhQpBasicMapEntity.class)).size();
            sql.append(orderSql);
            List<DzqhQpBasicMapEntity> apAsicMapList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhQpBasicMapEntity.class));
            for (DzqhQpBasicMapEntity asicMap : apAsicMapList) {
                String region = asicMap.getAdministrativeRegion();
                asicMap.setAdministrativeRegion(sysAreaService.getAreaNameByAreaId(region));
            }
            HashMap<String, Object> resultMap = new HashMap();
            resultMap.put("total", total);
            resultMap.put("rows", apAsicMapList);
            return resultMap;
        }else{
            HashMap<String, Object> resultMap = new HashMap();
            Integer total = 0;
            List<DzqhQpBasicMapEntity> apAsicMapList = new ArrayList<DzqhQpBasicMapEntity>();
            resultMap.put("total", total);
            resultMap.put("rows", apAsicMapList);
            return resultMap;
        }
    }

    //局级成果孔位信息地图圈选查询
    @Override
    public HashMap<String, Object> queryBureauLevelResultsHlByGeomText(SaResultsHlVo saResultsHlVo, String geomText) {
        String userId = PlatformSessionContext.getUserID();
        String role = sysRoleService.getRoleByUserId(userId);
        String user = sUserService.getSUser(PlatformSessionContext.getUserID());
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        //单位id
        String orgId = sUser.getOrgId();
        //省级id
        String provinceId = sUser.getProvinceId();
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            String roleCode = (String) roleMap.get("roleCode");
            if (roleCode.equals(FxfzConstants.DZQH_GAME_GL_ROLE)){
                //局级管理员
                saResultsHlVo.setRole("2");
                break;
            }
        }
        if(!PlatformObjectUtils.isEmpty(saResultsHlVo.getRole()) && saResultsHlVo.getRole().equals("2")) {//局级管理员
            //局级成果孔位信息查询
            StringBuilder sql = new StringBuilder("select daclp.* from (select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,safety_rating as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,update_status,sub_status,create_user,model,geom_text from dzqh_ap_basic_map" +
                    " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,update_status,sub_status,create_user,model,geom_text from dzqh_qp_basic_map" +
                    " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,update_status,sub_status,create_user,model,geom_text from dzqh_xqh_basic_map) as dzqh" +
                    " left join dzqh_ap_crux_layer_pf daclp on daclp.project_id = dzqh.project_id" +
                    //" left join dzqh_ap_crux_sd dacsd on dacsd.project_id=dzqh.project_id" +
                    //" left join dzqh_ap_crux_sc dacsc on dacsc.project_id=dzqh.project_id" +
                    //" left join dzqh_ap_crux_sd dacs on dacs.project_id= dzqh.project_id" +
                    " where dzqh.sub_status='1' and dzqh.update_status='0'");
            //局级成果
            sql.append(" and (dzqh.province_id='"+provinceId+"' or dzqh.org_id='"+orgId+"')");

            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getProjectName())) {
                sql.append(" and dzqh.project_name like '%" + saResultsHlVo.getProjectName()+"%'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getHlLongitude())) {
                sql.append(" and daclp.hl_longitude >= '"+saResultsHlVo.getHlLongitude()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(saResultsHlVo.getEndHlLongitude())){
                sql.append(" and daclp.hl_longitude >= '"+saResultsHlVo.getEndHlLongitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getHlLatitude())) {
                sql.append(" and daclp.hl_latitude >= '"+saResultsHlVo.getHlLatitude()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(saResultsHlVo.getEndHlLatitude())){
                sql.append(" and daclp.hl_latitude <= '"+saResultsHlVo.getEndHlLatitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getBdosSample())) {
                sql.append(" and dacsd.bdos_sample = '" + saResultsHlVo.getBdosSample()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getApprovalStatus())) {
                sql.append(" and dzqh.approval_status ='" + saResultsHlVo.getApprovalStatus()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getStatus())) {
                sql.append(" and dzqh.status = '" + saResultsHlVo.getStatus()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getHlLongitude())) {
                sql.append(" and dzqh.longitude >= '"+saResultsHlVo.getHlLongitude()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(saResultsHlVo.getEndHlLongitude())){
                sql.append(" and dzqh.longitude <= '"+saResultsHlVo.getEndHlLongitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getHlLatitude())) {
                sql.append(" and dzqh.latitude >= '"+saResultsHlVo.getHlLatitude()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(saResultsHlVo.getEndHlLatitude())){
                sql.append(" and dzqh.latitude <= '"+saResultsHlVo.getEndHlLatitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getGeotechnicalName())) {
                sql.append(" and dacs.geotechnical_name like '%" + saResultsHlVo.getGeotechnicalName()+"%'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getFieldType())) {
                sql.append(" and dacsc.field_type = '" + saResultsHlVo.getFieldType()+"'");
            }

            //有孔位信息
            sql.append(" and daclp.project_id is not null and daclp.hl_longitude is not null and daclp.hl_latitude is not null");
            //地图圈选范围查询
            if (!PlatformObjectUtils.isEmpty(geomText)) {
                //项目信息相交
                //sql.append(" and dzqh.geom_text is not null and dzqh.geom_text!='' and ST_Intersects(st_geometryfromtext(dzqh.geom_text,4490),st_geometryfromtext('"+geomText+"',4490))");
                //孔位信息相交
                sql.append(" and ST_Intersects(st_geometryfromtext('POINT(' || daclp.hl_longitude || ' ' || daclp.hl_latitude || ')',4490),st_geometryfromtext('"+geomText+"',4490))");
            }
            //孔位信息
            StringBuilder orderSql = new StringBuilder(" order by daclp.project_id,daclp.create_time desc");
            sql.append(orderSql);
            Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhApCruxLayerPfEntity.class)).size();
            List<DzqhApCruxLayerPfEntity> cruxLayerPfList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhApCruxLayerPfEntity.class));
            HashMap<String, Object> resultMap = new HashMap();
            resultMap.put("total", total);
            resultMap.put("rows", cruxLayerPfList);
            return resultMap;
        }else{
            Integer total = 0;
            List<DzqhApCruxLayerPfEntity> cruxLayerPfList = new ArrayList<>();
            HashMap<String, Object> resultMap = new HashMap();
            resultMap.put("total", total);
            resultMap.put("rows", cruxLayerPfList);
            return resultMap;
        }
    }

    //省级成果项目信息地图圈选查询
    @Override
    public HashMap<String, Object> queryProvinceResultsProjectByGeomText(DzqhQpBasicMapEntity mapEntity, String geomText) {
        String userId = PlatformSessionContext.getUserID();
        String role = sysRoleService.getRoleByUserId(userId);
        String user = sUserService.getSUser(PlatformSessionContext.getUserID());
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        //单位id
        String orgId = sUser.getOrgId();
        //省级id
        String provinceId = sUser.getProvinceId();
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            String roleCode = (String) roleMap.get("roleCode");
            if(roleCode.equals(FxfzConstants.DZQH_PRI_GL_ROLE)){
                //省级管理员
                mapEntity.setRole("1");
                break;
            }
        }
        if(!PlatformObjectUtils.isEmpty(mapEntity.getRole()) && mapEntity.getRole().equals("1")) {//省级管理员
            //省级成果项目信息查询
            StringBuilder sql = new StringBuilder("select DISTINCT(project_id),* from (select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,safety_rating as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,sub_status,update_status,create_user,model,geom_text from dzqh_ap_basic_map" +
                    " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,sub_status,update_status,create_user,model,geom_text from dzqh_qp_basic_map" +
                    " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,sub_status,update_status,create_user,model,geom_text from dzqh_xqh_basic_map) as dzqh" +
                    " where sub_status='1' and update_status='0'");
            //省级成果
            sql.append(" and province_id='"+provinceId+"'");

            if (!PlatformObjectUtils.isEmpty(mapEntity.getProjectName())) {
                sql.append(" and project_name like '%" + mapEntity.getProjectName() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getLongitude()) && !PlatformObjectUtils.isEmpty(mapEntity.getEndLongitude())) {
                sql.append(" and longitude between '"+mapEntity.getLongitude()+"' and '"+mapEntity.getEndLongitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getLatitude()) && !PlatformObjectUtils.isEmpty(mapEntity.getEndLatitude())) {
                sql.append(" and latitude between '"+mapEntity.getLatitude()+"' and '"+mapEntity.getEndLatitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getApprovalStatus())) {
                sql.append(" and approval_status ='" + mapEntity.getApprovalStatus() + "'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getReviewUnit())) {
                sql.append(" and review_unit like '%" + mapEntity.getReviewUnit() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getStatus())) {
                sql.append(" and status = '" + mapEntity.getStatus() + "'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getAdministrativeRegion())) {
                sql.append(" and administrative_region= '" + mapEntity.getAdministrativeRegion() + "'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getUndertaker())) {
                sql.append(" and undertaker like '%" + mapEntity.getUndertaker() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getRegistrant())) {
                sql.append(" and registrant like '%" + mapEntity.getRegistrant() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getRegistrationUnit())) {
                sql.append(" and registration_unit like '% " + mapEntity.getRegistrationUnit() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getSr())) {
                sql.append(" and sr ='" + mapEntity.getSr() + "'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getCreateStartTime()) && !PlatformObjectUtils.isEmpty(mapEntity.getCreateEndTime())) {
                sql.append(" and date(create_time) between '"+mapEntity.getCreateStartTime()+"' and '"+mapEntity.getCreateEndTime()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getSubStartTime()) && !PlatformObjectUtils.isEmpty(mapEntity.getSubEndTime())) {
                sql.append(" and sub_date >= '" + mapEntity.getSubStartTime() + "' and sub_date <='" + mapEntity.getSubEndTime() + "'");
            }

            //地图圈选范围查询
            if (!PlatformObjectUtils.isEmpty(geomText)) {
                //项目位置上传文件空间位置相交
                sql.append(" and geom_text is not null and geom_text!='' and ST_Intersects(st_geometryfromtext(geom_text,4490),st_geometryfromtext('"+geomText+"',4490))");
                //项目位置经纬度相交查询
                //sql.append(" and ST_Intersects(st_geometryfromtext('POINT(' || longitude || ' ' || latitude || ')',4490),st_geometryfromtext('"+geomText+"',4490))");
            }
            StringBuilder orderSql = new StringBuilder(" order by create_time desc");
            Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhQpBasicMapEntity.class)).size();
            sql.append(orderSql);
            List<DzqhQpBasicMapEntity> apAsicMapList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhQpBasicMapEntity.class));
            for (DzqhQpBasicMapEntity asicMap : apAsicMapList) {
                String region = asicMap.getAdministrativeRegion();
                asicMap.setAdministrativeRegion(sysAreaService.getAreaNameByAreaId(region));
            }
            HashMap<String, Object> resultMap = new HashMap();
            resultMap.put("total", total);
            resultMap.put("rows", apAsicMapList);
            return resultMap;
        }else{
            Integer total = 0;
            List<DzqhQpBasicMapEntity> apAsicMapList = new ArrayList<>();
            HashMap<String, Object> resultMap = new HashMap();
            resultMap.put("total", total);
            resultMap.put("rows", apAsicMapList);
            return resultMap;
        }
    }

    //省级成果孔位信息地图圈选查询
    @Override
    public HashMap<String, Object> queryProvinceResultsHlByGeomText(SaResultsHlVo saResultsHlVo, String geomText) {
        String userId = PlatformSessionContext.getUserID();
        String role = sysRoleService.getRoleByUserId(userId);
        String user = sUserService.getSUser(PlatformSessionContext.getUserID());
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        //单位id
        String orgId = sUser.getOrgId();
        //省级id
        String provinceId = sUser.getProvinceId();
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            String roleCode = (String) roleMap.get("roleCode");
            if(roleCode.equals(FxfzConstants.DZQH_PRI_GL_ROLE)){
                //省级管理员
                saResultsHlVo.setRole("1");
                break;
            }
        }
        if(!PlatformObjectUtils.isEmpty(saResultsHlVo.getRole()) && saResultsHlVo.getRole().equals("1")) {//省级管理员
            //省级成果孔位信息查询
            StringBuilder sql = new StringBuilder("select daclp.* from (select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,safety_rating as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,update_status,sub_status,create_user,model,geom_text from dzqh_ap_basic_map" +
                    " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,update_status,sub_status,create_user,model,geom_text from dzqh_qp_basic_map" +
                    " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,update_status,sub_status,create_user,model,geom_text from dzqh_xqh_basic_map) as dzqh" +
                    " left join dzqh_ap_crux_layer_pf daclp on daclp.project_id = dzqh.project_id" +
                    //" left join dzqh_ap_crux_sd dacsd on dacsd.project_id=dzqh.project_id" +
                    //" left join dzqh_ap_crux_sc dacsc on dacsc.project_id=dzqh.project_id" +
                    //" left join dzqh_ap_crux_sd dacs on dacs.project_id= dzqh.project_id" +
                    " where dzqh.sub_status='1' and dzqh.update_status='0'");
            //省级成果
            sql.append(" and dzqh.province_id='"+provinceId+"'");

            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getProjectName())) {
                sql.append(" and dzqh.project_name like '%" + saResultsHlVo.getProjectName()+"%'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getHlLongitude()) && !PlatformObjectUtils.isEmpty(saResultsHlVo.getEndHlLongitude())) {
                sql.append(" and daclp.hl_longitude between '"+saResultsHlVo.getHlLongitude()+"' and '"+saResultsHlVo.getEndHlLongitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getHlLatitude()) && !PlatformObjectUtils.isEmpty(saResultsHlVo.getEndHlLatitude())) {
                sql.append(" and daclp.hl_latitude between '"+saResultsHlVo.getHlLatitude()+"' and '"+saResultsHlVo.getEndHlLatitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getBdosSample())) {
                sql.append(" and dacsd.bdos_sample = '" + saResultsHlVo.getBdosSample()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getApprovalStatus())) {
                sql.append(" and dzqh.approval_status ='" + saResultsHlVo.getApprovalStatus()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getStatus())) {
                sql.append(" and dzqh.status = '" + saResultsHlVo.getStatus()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getHlLongitude()) && !PlatformObjectUtils.isEmpty(saResultsHlVo.getEndHlLongitude())) {
                sql.append(" and dzqh.longitude between '"+saResultsHlVo.getHlLongitude()+"' and '"+saResultsHlVo.getEndHlLongitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getHlLatitude()) && !PlatformObjectUtils.isEmpty(saResultsHlVo.getEndHlLatitude())) {
                sql.append(" and dzqh.latitude between '"+saResultsHlVo.getHlLatitude()+"' and '"+saResultsHlVo.getEndHlLatitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getGeotechnicalName())) {
                sql.append(" and dacs.geotechnical_name like '%" + saResultsHlVo.getGeotechnicalName()+"%'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getFieldType())) {
                sql.append(" and dacsc.field_type = '" + saResultsHlVo.getFieldType()+"'");
            }

            //有孔位信息
            sql.append(" and daclp.project_id is not null and daclp.hl_longitude is not null and daclp.hl_latitude is not null");
            //地图圈选范围查询
            if (!PlatformObjectUtils.isEmpty(geomText)) {
                //项目信息相交
                //sql.append(" and dzqh.geom_text is not null and dzqh.geom_text!='' and ST_Intersects(st_geometryfromtext(dzqh.geom_text,4490),st_geometryfromtext('"+geomText+"',4490))");
                //孔位信息相交
                sql.append(" and ST_Intersects(st_geometryfromtext('POINT(' || daclp.hl_longitude || ' ' || daclp.hl_latitude || ')',4490),st_geometryfromtext('"+geomText+"',4490))");
            }
            //安评孔位信息
            StringBuilder orderSql = new StringBuilder(" order by daclp.project_id,daclp.create_time desc");
            sql.append(orderSql);
            Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhApCruxLayerPfEntity.class)).size();
            List<DzqhApCruxLayerPfEntity> cruxLayerPfList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhApCruxLayerPfEntity.class));
            HashMap<String, Object> resultMap = new HashMap();
            resultMap.put("total", total);
            resultMap.put("rows", cruxLayerPfList);
            return resultMap;
        }else{
            Integer total = 0;
            List<DzqhApCruxLayerPfEntity> cruxLayerPfList = new ArrayList<>();
            HashMap<String, Object> resultMap = new HashMap();
            resultMap.put("total", total);
            resultMap.put("rows", cruxLayerPfList);
            return resultMap;
        }
    }

    //管理员项目信息地图圈选查询
    @Override
    public HashMap<String, Object> queryAdminProjectByGeomText(DzqhQpBasicMapEntity mapEntity, String geomText) {
        String userId = PlatformSessionContext.getUserID();
        String role = sysRoleService.getRoleByUserId(userId);
        String user = sUserService.getSUser(PlatformSessionContext.getUserID());
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        //单位id
        String orgId = sUser.getOrgId();
        //省级id
        String provinceId = sUser.getProvinceId();
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            String roleCode = (String) roleMap.get("roleCode");
            if (roleCode.equals(FxfzConstants.DZQH_GL_ROLE)) {
                //系统管理员
                mapEntity.setRole("0");
                break;
            }
        }
        if(!PlatformObjectUtils.isEmpty(mapEntity.getRole()) && mapEntity.getRole().equals("0")) {//系统管理员
            //管理员项目信息查询
            StringBuilder sql = new StringBuilder("select DISTINCT(project_id),* from (select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,safety_rating as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,sub_status,update_status,create_user,model,geom_text from dzqh_ap_basic_map" +
                    " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,sub_status,update_status,create_user,model,geom_text from dzqh_qp_basic_map" +
                    " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,sub_status,update_status,create_user,model,geom_text from dzqh_xqh_basic_map) as dzqh" +
                    " where sub_status='1' and update_status='0'");

            if (!PlatformObjectUtils.isEmpty(mapEntity.getProjectName())) {
                sql.append(" and project_name like '%" + mapEntity.getProjectName() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getLongitude())) {
                sql.append(" and longitude >= '"+mapEntity.getLongitude()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(mapEntity.getEndLongitude())){
                sql.append(" and longitude <= '"+mapEntity.getLongitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getLatitude())) {
                sql.append(" and latitude >= '"+mapEntity.getLatitude()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(mapEntity.getEndLatitude())){
                sql.append(" and latitude <= '"+mapEntity.getEndLatitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getApprovalStatus())) {
                sql.append(" and approval_status ='" + mapEntity.getApprovalStatus() + "'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getReviewUnit())) {
                sql.append(" and review_unit like '%" + mapEntity.getReviewUnit() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getStatus())) {
                sql.append(" and status = '" + mapEntity.getStatus() + "'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getAdministrativeRegion())) {
                sql.append(" and administrative_region= '" + mapEntity.getAdministrativeRegion() + "'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getUndertaker())) {
                sql.append(" and undertaker like '%" + mapEntity.getUndertaker() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getRegistrant())) {
                sql.append(" and registrant like '%" + mapEntity.getRegistrant() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getRegistrationUnit())) {
                sql.append(" and registration_unit like '%" + mapEntity.getRegistrationUnit() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getSr())) {
                sql.append(" and sr ='" + mapEntity.getSr() + "'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getCreateStartTime())) {
                sql.append(" and date(create_time) >= '" + mapEntity.getCreateStartTime() + "'");
            }
            if(!PlatformObjectUtils.isEmpty(mapEntity.getCreateEndTime())){
                sql.append(" and date(create_time) <= '" + mapEntity.getCreateEndTime() + "'");
            }
            if (!PlatformObjectUtils.isEmpty(mapEntity.getSubStartTime())) {
                sql.append(" and sub_date >= '" + mapEntity.getSubStartTime()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(mapEntity.getSubEndTime())){
                sql.append(" and sub_date <= '" + mapEntity.getSubEndTime()+"'");
            }

            //系统管理员
            //地图圈选范围查询
            if (!PlatformObjectUtils.isEmpty(geomText)) {
                //项目位置上传文件空间位置相交
                sql.append(" and geom_text is not null and geom_text!='' and ST_Intersects(st_geometryfromtext(geom_text,4490),st_geometryfromtext('"+geomText+"',4490))");
                //项目位置经纬度相交查询
                //sql.append(" and ST_Intersects(st_geometryfromtext('POINT(' || longitude || ' ' || latitude || ')',4490),st_geometryfromtext('"+geomText+"',4490))");
            }
            StringBuilder orderSql = new StringBuilder(" order by create_time desc");
            Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhQpBasicMapEntity.class)).size();
            sql.append(orderSql);
            List<DzqhQpBasicMapEntity> apAsicMapList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhQpBasicMapEntity.class));
            for (DzqhQpBasicMapEntity asicMap : apAsicMapList) {
                String region = asicMap.getAdministrativeRegion();
                asicMap.setAdministrativeRegion(sysAreaService.getAreaNameByAreaId(region));
            }
            HashMap<String, Object> resultMap = new HashMap();
            resultMap.put("total", total);
            resultMap.put("rows", apAsicMapList);
            return resultMap;
        }else{
            Integer total = 0;
            List<DzqhQpBasicMapEntity> apAsicMapList = new ArrayList<>();
            HashMap<String, Object> resultMap = new HashMap();
            resultMap.put("total", total);
            resultMap.put("rows", apAsicMapList);
            return resultMap;
        }
    }

    //管理员孔位信息地图圈选查询
    @Override
    public HashMap<String, Object> queryAdminHlByGeomText(SaResultsHlVo saResultsHlVo, String geomText) {
        String userId = PlatformSessionContext.getUserID();
        String role = sysRoleService.getRoleByUserId(userId);
        String user = sUserService.getSUser(PlatformSessionContext.getUserID());
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        //单位id
        String orgId = sUser.getOrgId();
        //省级id
        String provinceId = sUser.getProvinceId();
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            String roleCode = (String) roleMap.get("roleCode");
            if (roleCode.equals(FxfzConstants.DZQH_GL_ROLE)) {
                //系统管理员
                saResultsHlVo.setRole("0");
                break;
            }
        }
        if(!PlatformObjectUtils.isEmpty(saResultsHlVo.getRole()) && saResultsHlVo.getRole().equals("0")) {//系统管理员
            //管理员孔位信息查询
            StringBuilder sql = new StringBuilder("select daclp.* from (select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,safety_rating as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,update_status,sub_status,create_user,model,geom_text from dzqh_ap_basic_map" +
                    " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,update_status,sub_status,create_user,model,geom_text from dzqh_qp_basic_map" +
                    " UNION ALL select project_id,project_name,longitude,latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,update_status,sub_status,create_user,model,geom_text from dzqh_xqh_basic_map) as dzqh" +
                    " left join dzqh_ap_crux_layer_pf daclp on daclp.project_id = dzqh.project_id" +
                    //" left join dzqh_ap_crux_sd dacsd on dacsd.project_id=dzqh.project_id" +
                    //" left join dzqh_ap_crux_sc dacsc on dacsc.project_id=dzqh.project_id" +
                    //" left join dzqh_ap_crux_sd dacs on dacs.project_id= dzqh.project_id" +
                    " where dzqh.sub_status='1' and dzqh.update_status='0'");
            //系统管理员
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getProjectName())) {
                sql.append(" and dzqh.project_name like '%" + saResultsHlVo.getProjectName()+"%'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getHlLongitude()) && !PlatformObjectUtils.isEmpty(saResultsHlVo.getEndHlLongitude())) {
                sql.append(" and daclp.hl_longitude between '"+saResultsHlVo.getHlLongitude()+"' and '"+saResultsHlVo.getEndHlLongitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getHlLatitude()) && !PlatformObjectUtils.isEmpty(saResultsHlVo.getEndHlLatitude())) {
                sql.append(" and daclp.hl_latitude between '"+saResultsHlVo.getHlLatitude()+"' and '"+saResultsHlVo.getEndHlLatitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getBdosSample())) {
                sql.append(" and dacsd.bdos_sample = '" + saResultsHlVo.getBdosSample()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getApprovalStatus())) {
                sql.append(" and dzqh.approval_status ='" + saResultsHlVo.getApprovalStatus()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getStatus())) {
                sql.append(" and dzqh.status = '" + saResultsHlVo.getStatus()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getHlLongitude()) && !PlatformObjectUtils.isEmpty(saResultsHlVo.getEndHlLongitude())) {
                sql.append(" and dzqh.longitude between '"+saResultsHlVo.getHlLongitude()+"' and '"+saResultsHlVo.getEndHlLongitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getHlLatitude()) && !PlatformObjectUtils.isEmpty(saResultsHlVo.getEndHlLatitude())) {
                sql.append(" and dzqh.latitude between '"+saResultsHlVo.getHlLatitude()+"' and '"+saResultsHlVo.getEndHlLatitude()+"'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getGeotechnicalName())) {
                sql.append(" and dacs.geotechnical_name like '%" + saResultsHlVo.getGeotechnicalName()+"%'");
            }
            if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getFieldType())) {
                sql.append(" and dacsc.field_type = '" + saResultsHlVo.getFieldType()+"'");
            }
            //有孔位信息
            sql.append(" and daclp.project_id is not null and daclp.hl_longitude is not null and daclp.hl_latitude is not null");
            //地图圈选范围查询
            if (!PlatformObjectUtils.isEmpty(geomText)) {
                //项目信息相交
                //sql.append(" and dzqh.geom_text is not null and dzqh.geom_text!='' and ST_Intersects(st_geometryfromtext(dzqh.geom_text,4490),st_geometryfromtext('"+geomText+"',4490))");
                //孔位信息相交
                sql.append(" and ST_Intersects(st_geometryfromtext('POINT(' || daclp.hl_longitude || ' ' || daclp.hl_latitude || ')',4490),st_geometryfromtext('"+geomText+"',4490))");
            }
            //安评孔位信息
            StringBuilder orderSql = new StringBuilder(" order by daclp.project_id,daclp.create_time desc");
            sql.append(orderSql);
            Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhApCruxLayerPfEntity.class)).size();
            List<DzqhApCruxLayerPfEntity> cruxLayerPfList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhApCruxLayerPfEntity.class));
            HashMap<String, Object> resultMap = new HashMap();
            resultMap.put("total", total);
            resultMap.put("rows", cruxLayerPfList);
            return resultMap;
        }else{
            Integer total = 0;
            List<DzqhApCruxLayerPfEntity> cruxLayerPfList = new ArrayList<>();
            HashMap<String, Object> resultMap = new HashMap();
            resultMap.put("total", total);
            resultMap.put("rows", cruxLayerPfList);
            return resultMap;
        }
    }
}
